-- @owner: opentestcase041
-- @date: 2022-02-15
-- @testpoint: 账本数据库基础语法适配(DDL/DML)--函数/过程中修改防篡改表,合理报错
-- @modified: by opentestcase033 2023/04/25 修复存储过程报错位置不准确的问题，更新step11报错信息行号

--step1: 查询三权分立参数enableseparationofduty; expect:显示默认值off
show enableseparationofduty;

--step2: 创建不同权限用户; expect:用户创建成功
drop user if exists u_security_tpf0031_sys;
drop user if exists u_security_tpf0031_audit;
drop user if exists u_security_tpf0031_noruser;
create user u_security_tpf0031_sys with sysadmin password 'test@123';
create user u_security_tpf0031_audit with auditadmin password 'test@123';
create user u_security_tpf0031_noruser password 'test@123';

--step3: 系统管理员创建schema并指定防篡改选项; expect:成功
set role u_security_tpf0031_sys password 'test@123';
select current_user;
drop schema if exists s_security_tpf0031;
create schema s_security_tpf0031 authorization u_security_tpf0031_noruser with blockchain;

--step4: 切换普通用户; expect:成功
set role u_security_tpf0031_noruser password 'test@123';
select current_user;

--step5: 函数过程中创建防篡改表; expect:成功
create or replace function f_security_tpf0031_func() returns void as $$
begin
create table s_security_tpf0031.students_03(col int);
end;
$$
language plpgsql;
/
--step6: 调用函数; expect:成功
call f_security_tpf0031_func();

--step7: 函数过程中insert防篡改表数据; expect:成功
create or replace function f_security_tpf0031_func_01() returns void as $$
 begin
 insert into s_security_tpf0031.students_03 values  (3);
 end;
 $$
 language plpgsql;
/
--step8: 调用函数; expect:失败
call f_security_tpf0031_func_01();

--step9: 创建procedure; expect:成功
create or replace procedure create_table()
security invoker
as
begin
     create table s_security_tpf0031.students_04 (col int);
end;
/
create procedure insert_data(v integer)
security invoker
as
begin
    insert into s_security_tpf0031.students_04 values(v);
end;
/

--step10: 调用函数过程; expect:成功
call create_table();

--step11: 调用函数过程; expect:失败
call insert_data(3);

--step12: 审计用户查看用户历史表; expect:成功
set role u_security_tpf0031_audit password 'test@123';
select current_user;
select * from blockchain.s_security_tpf0031_students_03_hist;

--step13: 普通用户删除表，函数，存储过程; expect:成功
set role u_security_tpf0031_noruser password 'test@123';
select current_user;
drop table s_security_tpf0031.students_03 cascade;
drop table s_security_tpf0031.students_04 cascade;
drop procedure insert_data();
drop procedure create_table();
drop function f_security_tpf0031_func_01();
drop function f_security_tpf0031_func();

--step14: 重置当前用户; expect:重置成功
reset role;
select current_user;

--step15: 清理环境; expect:成功
drop schema s_security_tpf0031 cascade;
drop user u_security_tpf0031_sys cascade;
drop user u_security_tpf0031_audit cascade;
drop user u_security_tpf0031_noruser cascade;